

from SKO.AbstractDPJob import AbstractDPJob
import sys, datetime, json
import pandas as pd
import numpy as np
from numpy import array
import cvxpy as cp
from sqlalchemy import create_engine
from sqlalchemy.pool import NullPool
import os

class JianyanjiaoJob(AbstractDPJob):


    def __init__(self,
                 p_tmpl_no=None):

        super(JianyanjiaoJob, self).__init__()
        self.tmpl_no = p_tmpl_no
        pass


    def execute(self):
        return self.do_execute()


    def do_execute(self):

        super(JianyanjiaoJob, self).do_execute()

        tmpl_no = self.tmpl_no
        # # 数据库配置写死
        # DB_HOST_MPP_DB2 = '10.70.48.41'
        # DB_PORT_MPP_DB2 = 50021
        # DB_DBNAME_MPP_DB2 = 'BGBDPROD'
        # DB_USER_MPP_DB2 = 'g0mazzai'
        # DB_PASSWORD_MPP_DB2 = 'g0mazzaibg00'
        #
        # # 数据库连接函数写死
        # def getConnectionDb2(host, port, dbname, user, password):
        #     # conn = pg.connect(host=host, port=port, dbname=dbname, user=user, password=password)
        #     engine = create_engine('ibm_db_sa://' + user + ':' + password + '@' + host + ':' + str(port) + '/' + dbname,
        #                            encoding="utf-8", poolclass=NullPool)
        #     return engine.connect()
        #
        # db_conn_mpp = getConnectionDb2(DB_HOST_MPP_DB2,
        #                                DB_PORT_MPP_DB2,
        #                                DB_DBNAME_MPP_DB2,
        #                                DB_USER_MPP_DB2,
        #                                DB_PASSWORD_MPP_DB2)

        max_constant = 1000000
        # 读数据
        # sql = " select VAR,CLASS,SOURCE,PROD_DSCR,PROD_CODE,UNIT_PRICE,INIT_INV,INV_WT,MERGE_FLAG as OLD_FLAG " \
        #       " from BG00MAZZAI.T_ADS_WH_YLMX_COKE_PRICE2 " \
        #       " where TMPL_NO ='%s' and VAR !='其他参数' order by REC_ID " % (tmpl_no)
        # data_meizhong = pd.read_sql_query(sql, con=db_conn_mpp)
        data_meizhong = pd.read_excel('COKE2价格.xlsx')
        data_meizhong.columns = data_meizhong.columns.str.upper()
        # sql = " select VAR,PROD_DSCR,UNIT_PRICE " \
        #       " from BG00MAZZAI.T_ADS_WH_YLMX_COKE_PRICE2 " \
        #       " where TMPL_NO ='%s' and VAR='其他参数' " % (tmpl_no)
        # data_otherprice = pd.read_sql_query(sql, con=db_conn_mpp)
        data_otherprice = pd.read_excel('COKE2其他参数价格.xlsx')
        data_otherprice.columns = data_otherprice.columns.str.upper()
        data_other1 = data_otherprice[data_otherprice['PROD_DSCR'] == '外购焦炭']
        data_other1 = data_other1.reset_index(drop=True)
        price_waigoujiaotan = data_other1.loc[0]['UNIT_PRICE']
        # sql = " select VAR,CLASS,SOURCE,PROD_DSCR,PROD_CODE,MIN_VALUE,MAX_VALUE,FLAG " \
        #       " from BG00MAZZAI.T_ADS_WH_YLMX_COKE_RATIO2 " \
        #       " where TMPL_NO ='%s'  " % (tmpl_no)
        # data_bili = pd.read_sql_query(sql, con=db_conn_mpp)
        data_bili = pd.read_excel('COKE2比例上下限.xlsx')
        data_bili.columns = data_bili.columns.str.upper()
        # sql = " select VAR,CLASS,SOURCE,PROD_DSCR,PROD_CODE,H2O,ASH,COKE_VM,S, " \
        #       " COKING_COALBLD_BOND_IND,M_L3_JZCHD_Y,COKING_COALBLD_GIESFLU,L_M_AB,C,COKE_HOTVALUE " \
        #       " from BG00MAZZAI.T_ADS_WH_YLMX_COKE_IND_INFO2 " \
        #       " where TMPL_NO ='%s' " % (tmpl_no)
        # data_meizhi = pd.read_sql_query(sql, con=db_conn_mpp)
        data_meizhi = pd.read_excel('COKE2煤质.xlsx')
        data_meizhi.columns = data_meizhi.columns.str.upper()
        # sql = " select BIG_VAR,FLAG,H2O,ASH,COKE_VM,S, " \
        #       " COKING_COALBLD_BOND_IND,M_L3_JZCHD_Y,COKING_COALBLD_GIESFLU,L_M_AB,C,COKE_HOTVALUE " \
        #       " from BG00MAZZAI.T_ADS_WH_YLMX_COKE_INDLIMIT2 " \
        #       " where TMPL_NO ='%s' " % (tmpl_no)
        # data_shangxiaxian = pd.read_sql_query(sql, con=db_conn_mpp)
        data_shangxiaxian = pd.read_excel('COKE2煤质上下限.xlsx')
        data_shangxiaxian.columns = data_shangxiaxian.columns.str.upper()
        # sql = " select VAR,CLASS,SOURCE,PROD_DSCR,PROD_CODE,FE2O3,SIO2,CAO,AL2O3,MGO,FOP_TIO2, " \
        #       " MNO,P2O5,BF_SINTER_QLTY_S,ZN,PB,FOP_K2O,FOP_NA2O,VITRI_REFL,VITRI_COAL_OI " \
        #       " from BG00MAZZAI.T_ADS_WH_YLMX_ASH_CPETRO " \
        #       " where TMPL_NO ='%s' " % (tmpl_no)
        # data_huizhongchengfen = pd.read_sql_query(sql, con=db_conn_mpp)
        data_huizhongchengfen = pd.read_excel('COKE2灰中成分煤岩分析.xlsx')
        data_huizhongchengfen.columns = data_huizhongchengfen.columns.str.upper()
        # sql = " select PARM_CHN,PARM_CALC " \
        #       " from BG00MAZZAI.T_ADS_WH_YLMX_COKE_COEF2 " \
        #       " where TMPL_NO ='original' "
        # data_jiaotancanshu = pd.read_sql_query(sql, con=db_conn_mpp)
        data_jiaotancanshu = pd.read_excel('COKE2参数.xlsx')
        data_jiaotancanshu.columns = data_jiaotancanshu.columns.str.upper()
        parm_1 = data_jiaotancanshu[(data_jiaotancanshu['PARM_CHN'] == 'parm_1')]
        parm_1 = parm_1['PARM_CALC'].values[0]
        parm_2 = data_jiaotancanshu[(data_jiaotancanshu['PARM_CHN'] == 'parm_2')]
        parm_2 = parm_2['PARM_CALC'].values[0]
        parm_3 = data_jiaotancanshu[(data_jiaotancanshu['PARM_CHN'] == 'parm_3')]
        parm_3 = parm_3['PARM_CALC'].values[0]
        parm_4 = data_jiaotancanshu[(data_jiaotancanshu['PARM_CHN'] == 'parm_4')]
        parm_4 = parm_4['PARM_CALC'].values[0]
        parm_5 = data_jiaotancanshu[(data_jiaotancanshu['PARM_CHN'] == 'parm_5')]
        parm_5 = parm_5['PARM_CALC'].values[0]
        parm_6 = data_jiaotancanshu[(data_jiaotancanshu['PARM_CHN'] == 'parm_6')]
        parm_6 = parm_6['PARM_CALC'].values[0]
        parm_7 = data_jiaotancanshu[(data_jiaotancanshu['PARM_CHN'] == 'parm_7')]
        parm_7 = parm_7['PARM_CALC'].values[0]
        parm_8 = data_jiaotancanshu[(data_jiaotancanshu['PARM_CHN'] == 'parm_8')]
        parm_8 = parm_8['PARM_CALC'].values[0]
        parm_10 = data_jiaotancanshu[(data_jiaotancanshu['PARM_CHN'] == 'parm_10')]
        parm_10 = parm_10['PARM_CALC'].values[0]
        parm_11 = data_jiaotancanshu[(data_jiaotancanshu['PARM_CHN'] == 'parm_11')]
        parm_11 = parm_11['PARM_CALC'].values[0]
        parm_12 = data_jiaotancanshu[(data_jiaotancanshu['PARM_CHN'] == 'parm_12')]
        parm_12 = parm_12['PARM_CALC'].values[0]
        parm_13 = data_jiaotancanshu[(data_jiaotancanshu['PARM_CHN'] == 'parm_13')]
        parm_13 = parm_13['PARM_CALC'].values[0]
        parm_14 = data_jiaotancanshu[(data_jiaotancanshu['PARM_CHN'] == 'parm_14')]
        parm_14 = parm_14['PARM_CALC'].values[0]
        parm_15 = data_jiaotancanshu[(data_jiaotancanshu['PARM_CHN'] == 'parm_15')]
        parm_15 = parm_15['PARM_CALC'].values[0]
        parm_16 = data_jiaotancanshu[(data_jiaotancanshu['PARM_CHN'] == 'parm_16')]
        parm_16 = parm_16['PARM_CALC'].values[0]
        parm_17 = data_jiaotancanshu[(data_jiaotancanshu['PARM_CHN'] == 'parm_17')]
        parm_17 = parm_17['PARM_CALC'].values[0]
        parm_19 = data_jiaotancanshu[(data_jiaotancanshu['PARM_CHN'] == 'parm_19')]
        parm_19 = parm_19['PARM_CALC'].values[0]
        parm_20 = data_jiaotancanshu[(data_jiaotancanshu['PARM_CHN'] == 'parm_20')]
        parm_20 = parm_20['PARM_CALC'].values[0]
        parm_21 = data_jiaotancanshu[(data_jiaotancanshu['PARM_CHN'] == 'parm_21')]
        parm_21 = parm_21['PARM_CALC'].values[0]
        parm_22 = data_jiaotancanshu[(data_jiaotancanshu['PARM_CHN'] == 'parm_22')]
        parm_22 = parm_22['PARM_CALC'].values[0]
        parm_23 = data_jiaotancanshu[(data_jiaotancanshu['PARM_CHN'] == 'parm_23')]
        parm_23 = parm_23['PARM_CALC'].values[0]
        parm_24 = data_jiaotancanshu[(data_jiaotancanshu['PARM_CHN'] == 'parm_24')]
        parm_24 = parm_24['PARM_CALC'].values[0]
        parm_25 = data_jiaotancanshu[(data_jiaotancanshu['PARM_CHN'] == 'parm_25')]
        parm_25 = parm_25['PARM_CALC'].values[0]
        parm_26 = data_jiaotancanshu[(data_jiaotancanshu['PARM_CHN'] == 'parm_26')]
        parm_26 = parm_26['PARM_CALC'].values[0]
        parm_27 = data_jiaotancanshu[(data_jiaotancanshu['PARM_CHN'] == 'parm_27')]
        parm_27 = parm_27['PARM_CALC'].values[0]
        parm_28 = data_jiaotancanshu[(data_jiaotancanshu['PARM_CHN'] == 'parm_28')]
        parm_28 = parm_28['PARM_CALC'].values[0]
        parm_29 = data_jiaotancanshu[(data_jiaotancanshu['PARM_CHN'] == 'parm_29')]
        parm_29 = parm_29['PARM_CALC'].values[0]

        parm_31 = data_jiaotancanshu[(data_jiaotancanshu['PARM_CHN'] == 'parm_31')]
        parm_31 = parm_31['PARM_CALC'].values[0]
        parm_32 = data_jiaotancanshu[(data_jiaotancanshu['PARM_CHN'] == 'parm_32')]
        parm_32 = parm_32['PARM_CALC'].values[0]
        parm_33 = data_jiaotancanshu[(data_jiaotancanshu['PARM_CHN'] == 'parm_33')]
        parm_33 = parm_33['PARM_CALC'].values[0]
        parm_34 = data_jiaotancanshu[(data_jiaotancanshu['PARM_CHN'] == 'parm_34')]
        parm_34 = parm_34['PARM_CALC'].values[0]
        parm_35 = data_jiaotancanshu[(data_jiaotancanshu['PARM_CHN'] == 'parm_35')]
        parm_35 = parm_35['PARM_CALC'].values[0]
        parm_36 = data_jiaotancanshu[(data_jiaotancanshu['PARM_CHN'] == 'parm_36')]
        parm_36 = parm_36['PARM_CALC'].values[0]
        parm_37 = data_jiaotancanshu[(data_jiaotancanshu['PARM_CHN'] == 'parm_37')]
        parm_37 = parm_37['PARM_CALC'].values[0]
        parm_38 = data_jiaotancanshu[(data_jiaotancanshu['PARM_CHN'] == 'parm_38')]
        parm_38 = parm_38['PARM_CALC'].values[0]
        parm_39 = data_jiaotancanshu[(data_jiaotancanshu['PARM_CHN'] == 'parm_39')]
        parm_39 = parm_39['PARM_CALC'].values[0]
        parm_40 = data_jiaotancanshu[(data_jiaotancanshu['PARM_CHN'] == 'parm_40')]
        parm_40 = parm_40['PARM_CALC'].values[0]
        parm_41 = data_jiaotancanshu[(data_jiaotancanshu['PARM_CHN'] == 'parm_41')]
        parm_41 = parm_41['PARM_CALC'].values[0]
        parm_42 = data_jiaotancanshu[(data_jiaotancanshu['PARM_CHN'] == 'parm_42')]
        parm_42 = parm_42['PARM_CALC'].values[0]
        parm_43 = data_jiaotancanshu[(data_jiaotancanshu['PARM_CHN'] == 'parm_43')]
        parm_43 = parm_43['PARM_CALC'].values[0]
        parm_44 = data_jiaotancanshu[(data_jiaotancanshu['PARM_CHN'] == 'parm_44')]
        parm_44 = parm_44['PARM_CALC'].values[0]
        parm_45 = data_jiaotancanshu[(data_jiaotancanshu['PARM_CHN'] == 'parm_45')]
        parm_45 = parm_45['PARM_CALC'].values[0]
        parm_46 = data_jiaotancanshu[(data_jiaotancanshu['PARM_CHN'] == 'parm_46')]
        parm_46 = parm_46['PARM_CALC'].values[0]
        parm_47 = data_jiaotancanshu[(data_jiaotancanshu['PARM_CHN'] == 'parm_47')]
        parm_47 = parm_47['PARM_CALC'].values[0]
        parm_48 = data_jiaotancanshu[(data_jiaotancanshu['PARM_CHN'] == 'parm_48')]
        parm_48 = parm_48['PARM_CALC'].values[0]
        QRT1 = data_jiaotancanshu[(data_jiaotancanshu['PARM_CHN'] == 'QRT1')]
        QRT1 = QRT1['PARM_CALC'].values[0]
        QRT2 = data_jiaotancanshu[(data_jiaotancanshu['PARM_CHN'] == 'QRT2')]
        QRT2 = QRT2['PARM_CALC'].values[0]
        QRT3 = data_jiaotancanshu[(data_jiaotancanshu['PARM_CHN'] == 'QRT3')]
        QRT4 = QRT3['PARM_CALC'].values[0]
        QRT4 = data_jiaotancanshu[(data_jiaotancanshu['PARM_CHN'] == 'QRT4')]
        QRT4 = QRT4['PARM_CALC'].values[0]
        # 去除资源量为0的品种，减少变量
        # 月初库存
        data_meizhong.INIT_INV.fillna(0, inplace=True)
        # 可供资源
        data_meizhong.INV_WT.fillna(0, inplace=True)

        data_qianti = pd.read_excel('COKE2前提条件.xlsx')
        data_qianti.columns = data_qianti.columns.str.upper()

        data_zhuyao = pd.read_excel('COKE2主要参数.xlsx')
        data_zhuyao.columns = data_zhuyao.columns.str.upper()

        # 年度前提条件表
        kongmeizhibiao = data_qianti.loc[0]['TOTAL_COAL_AMT']
        tieshuichanliang = data_qianti.loc[0]['MOLTIRON_WT']
        jiaotanchanliang = data_qianti.loc[0]['COKE_OUTPUT']
        fadianliang = data_qianti.loc[0]['ELECOUT']
        shaojiechanliang = data_qianti.loc[0]['SINTER_OUTPUT']
        zongmeibi = data_qianti.loc[0]['BF_PCR']
        CDQbi = data_qianti.loc[0]['CDQ_RATE']

        # 纯煤比 = 总煤比 - CDQ比
        chunmeibi = data_qianti.loc[0]['BF_PCR1']
        zongjiaobi = data_qianti.loc[0]['BF_CR']
        shaojieranliaobi = data_qianti.loc[0]['SINTER_FUEL_RATIO']
        # 主要参数表
        chengjiaolv = data_zhuyao.loc[0]['COKING_COKEYR']
        yejinjiaolv = data_zhuyao.loc[0]['COKING_METCOKRAT']
        cujiaolv = data_zhuyao.loc[0]['SINTER_CRSCOKE_RATIO']
        meidianbi = data_zhuyao.loc[0]['COAL_ELEC_RATIO']
        fadianmeihao = data_zhuyao.loc[0]['COAL_USE']
        lianjiaomeishuifen = data_zhuyao.loc[0]['COKING_COALBLD_MOIST']
        penchuimeishuifen = data_zhuyao.loc[0]['BF_COALINJECT_MOIST']
        shaojiemeishuifen = data_zhuyao.loc[0]['SINTER_COAL_MOIST']
        penchui_meitanwushunlv = data_zhuyao.loc[0]['COAL_LOSS_RATE1']
        fadian_meitanwushunlv = data_zhuyao.loc[0]['COAL_LOSS_RATE2']
        shaojie_meitanwushunlv = data_zhuyao.loc[0]['COAL_LOSS_RATE3']
        lianjiao_meitanwushunlv = data_zhuyao.loc[0]['COAL_LOSS_RATE4']
        waigoujiaoshuifen = data_zhuyao.loc[0]['PCOKE_MOIST']
        waigoujiaofenlv = data_zhuyao.loc[0]['PCOKE_COKEPOWD_RATE']
        shaojiejiaofenshuifen = data_zhuyao.loc[0]['SINTER_COKE_MOIST']
        lantanshuifen = data_zhuyao.loc[0]['SEMICOKE_MOIST']
        jiaotanchaochanlv = data_zhuyao.loc[0]['COKING_OVERPROD_RATE']

        CAL_WT_lianjiaomei = jiaotanchanliang / chengjiaolv * 100 / (100 - lianjiaomeishuifen) * 100 * (
                100 + lianjiao_meitanwushunlv) / 100
        WT_lianjiaomei = CAL_WT_lianjiaomei * (100 + jiaotanchaochanlv) / 100
        # 外购焦炭    （补炼焦煤）
        WT_waigoujiaotan = (tieshuichanliang * zongjiaobi / 1000 - jiaotanchanliang * yejinjiaolv / 100) / (
                100 - waigoujiaofenlv) * 100 / (100 - waigoujiaoshuifen) * 100
        def __cal_resource_wt(x):
            rst = x.INIT_INV + x.INV_WT
            return rst
        data_meizhong['MAX_WT'] = data_meizhong.apply(lambda x: __cal_resource_wt(x), axis=1)


        data_meizhong = data_meizhong[(data_meizhong['MAX_WT'] > 0)]
        data_meizhong = data_meizhong.reset_index(drop=True)
        # ganmeiliang = data_meizhong['PLAN_WT'].sum()
        # peihemei_wt = data_meizhong['PLAN_WT'].sum()
        peihemei_wt = WT_lianjiaomei
        my_list = data_meizhong['OLD_FLAG'].values.tolist()
        print(my_list)
        import numpy as np
        new_list = [x for x in my_list if not np.isnan(x)]
        print(new_list)
        sorted_unique_list = sorted(set(new_list))
        print(sorted_unique_list)
        def __cal_new_merge_flag(x):
            rst = 0
            if x.OLD_FLAG in sorted_unique_list:
                rst = sorted_unique_list.index(x.OLD_FLAG) + 1
            return rst
        data_meizhong['MERGE_FLAG'] = data_meizhong.apply(lambda x: __cal_new_merge_flag(x), axis=1)
        data_meizhong.drop(['OLD_FLAG'], axis=1, inplace=True)

        # 对煤种生成唯一编码mark
        # 维度上只考虑VAR性状，SOURCE来源
        # 写死维度种类对应关系？
        data_mark = data_meizhong.copy()

        def __cal_rank_xingzhuang(x):
            if x.VAR == '主焦':
                rst = 1
            elif x.VAR == '肥煤':
                rst = 2
            elif x.VAR == '1/3焦':
                rst = 3
            elif x.VAR == '气煤':
                rst = 4
            elif x.VAR == '瘦煤':
                rst = 5
            else:
                rst = 6
            return rst

        data_mark['xingzhuang_rank'] = data_mark.apply(lambda x: __cal_rank_xingzhuang(x), axis=1)

        def __cal_rank_laiyuan(x):
            if x.SOURCE == '进口':
                rst = 1
            elif x.SOURCE == '大矿长协':
                rst = 2
            elif x.SOURCE == '大矿市场':
                rst = 3
            elif x.SOURCE == '地方矿':
                rst = 4
            else:
                rst = 5
            return rst

        data_mark['laiyuan_rank'] = data_mark.apply(lambda x: __cal_rank_laiyuan(x), axis=1)

        def __cal_rank_group(x):
            rst = str(x.xingzhuang_rank) + '_' + str(x.laiyuan_rank)
            return rst

        data_mark['group'] = data_mark.apply(lambda x: __cal_rank_group(x), axis=1)
        data_mark = data_mark.reset_index(drop=False)
        data_mark.rename(columns={'index': 'index_old'}, inplace=True)

        data_mark['rank0'] = data_mark['index_old'].groupby(data_mark['group']).rank()
        data_mark['pinming_rank'] = data_mark['rank0'].astype(int)

        def __cal_rank_mark(x):
            rst = str(x.group) + '_' + str(x.pinming_rank)
            return rst

        data_mark['mark'] = data_mark.apply(lambda x: __cal_rank_mark(x), axis=1)
        data_mark.drop(['rank0'], axis=1, inplace=True)
        # 统计个数，做个var_df以便后续对应
        var_list = ['主焦', '肥煤', '1/3焦', '气煤', '瘦煤']
        source_list = ['进口', '大矿长协', '大矿市场', '地方矿']
        var_df = pd.DataFrame(columns=['VAR', 'SOURCE', 'group', 'count'])
        dict = {}
        for i in range(1, len(var_list) + 1):
            for j in range(1, len(source_list) + 1):
                df = data_mark[
                    (data_mark['VAR'] == var_list[i - 1]) & (data_mark['SOURCE'] == source_list[j - 1])]
                row_count = df.shape[0]
                # print("行数：", row_count)
                dict['VAR'] = var_list[i - 1]
                dict['SOURCE'] = source_list[j - 1]
                dict['group'] = str(i) + '_' + str(j)
                dict['count'] = row_count
                new_row = pd.Series(dict)
                var_df = var_df.append(new_row, ignore_index=True)
        # 生成动态参数
        # 资源量，分配比例上下限
        # 资源量，按照品名的
        data_ziyuanliang_1 = data_mark[
            ['PROD_CODE', 'INIT_INV', 'INV_WT', 'MERGE_FLAG', 'MAX_WT', 'mark']]
        data_ziyuanliang_1.MAX_WT.fillna(0, inplace=True)
        for index, row in data_ziyuanliang_1.iterrows():
            exec('ziyuanliang_{} ={}'.format(row['mark'], row['MAX_WT']))
        # 资源量，合并单元格的
        merge_num = data_mark["MERGE_FLAG"].max()
        print(merge_num)
        for i in range(1, int(merge_num) + 1):
            print(i)
            data_ziyuanliang_2 = data_ziyuanliang_1[(data_ziyuanliang_1['MERGE_FLAG'] == i)]
            data_ziyuanliang_2 = data_ziyuanliang_2.reset_index(drop=True)
            tmp_merge_ziyuanliang = data_ziyuanliang_2.loc[0]['INV_WT']
            exec('merge_ziyuanliang_{} ={}'.format(i, tmp_merge_ziyuanliang))
        # 分配比例上下限，按照品名的
        data_bili.rename(columns={'MAX_VALUE': 'UL'}, inplace=True)
        data_bili.rename(columns={'MIN_VALUE': 'LL'}, inplace=True)
        data_bili.UL.fillna(100, inplace=True)
        data_bili.LL.fillna(0, inplace=True)
        data_bili_1 = data_bili[(data_bili['FLAG'] == '品名')]
        data_bili_1 = data_bili_1.reset_index(drop=True)
        data_bili_1 = data_bili_1[['PROD_CODE', 'UL', 'LL']]
        v = ['PROD_CODE']
        df3 = pd.merge(data_mark, data_bili_1, on=v, how='left')
        for index, row in df3.iterrows():
            exec('ul_{} ={}'.format(row['mark'], row['UL']))
            exec('ll_{} ={}'.format(row['mark'], row['LL']))
        # 分配比例上下限，按照性状的
        for i in range(0, len(var_list)):
            print(var_list[i])
            tmp_var = var_list[i]
            data_bili_2 = data_bili[(data_bili['FLAG'] == '性状') & (data_bili['VAR'] == tmp_var)]
            data_bili_2 = data_bili_2.reset_index(drop=True)
            tmp_ul = data_bili_2.loc[0]['UL']
            tmp_ll = data_bili_2.loc[0]['LL']
            exec('ul_var_{} ={}'.format(i + 1, tmp_ul))
            exec('ll_var_{} ={}'.format(i + 1, tmp_ll))
        # 分配比例上下限，按照来源的
        for i in range(0, len(source_list)):
            print(source_list[i])
            tmp_source = source_list[i]
            data_bili_3 = data_bili[(data_bili['FLAG'] == '来源') & (data_bili['SOURCE'] == tmp_source)]
            data_bili_3 = data_bili_3.reset_index(drop=True)
            tmp_ul = data_bili_3.loc[0]['UL']
            tmp_ll = data_bili_3.loc[0]['LL']
            exec('ul_source_{} ={}'.format(i + 1, tmp_ul))
            exec('ll_source_{} ={}'.format(i + 1, tmp_ll))
        # 分配比例上下限，按照性状来源的

        # var_df_1 = var_df[(var_df['count'] > 0)]
        # var_df_1 = var_df_1.reset_index(drop=True)

        var_df1 = var_df.copy()

        def __cal_new_var(x):
            rst = str(x.VAR) + '_' + str(x.SOURCE)
            return rst

        var_df1['NEW_VAR'] = var_df1.apply(lambda x: __cal_new_var(x), axis=1)

        data_bili_4 = data_bili[(data_bili['FLAG'] == '性状来源')]
        data_bili_4 = data_bili_4.reset_index(drop=True)

        def __cal_new_var(x):
            rst = str(x.VAR) + '_' + str(x.SOURCE)
            return rst

        data_bili_4['NEW_VAR'] = data_bili_4.apply(lambda x: __cal_new_var(x), axis=1)
        data_bili_4 = data_bili_4[['NEW_VAR', 'UL', 'LL']]
        v = ['NEW_VAR']
        df3 = pd.merge(var_df1, data_bili_4, on=v, how='left')
        for index, row in df3.iterrows():
            exec('ul_group_{} ={}'.format(row['group'], row['UL']))
            exec('ll_group_{} ={}'.format(row['group'], row['LL']))
        data_bili_5 = data_bili[(data_bili['FLAG'] == '特殊')]
        data_bili_5 = data_bili_5.reset_index(drop=True)
        teshu_ul = data_bili_5.loc[0]['UL']
        teshu_ll = data_bili_5.loc[0]['LL']
        # 煤质
        data_meizhi.rename(columns={'H2O': 'shui'}, inplace=True)
        data_meizhi.rename(columns={'ASH': 'hui'}, inplace=True)
        data_meizhi.rename(columns={'COKE_VM': 'huifa'}, inplace=True)
        data_meizhi.rename(columns={'S': 'liu'}, inplace=True)
        data_meizhi.rename(columns={'COKING_COALBLD_BOND_IND': 'gzhi'}, inplace=True)
        data_meizhi.rename(columns={'M_L3_JZCHD_Y': 'yzhi'}, inplace=True)
        data_meizhi.rename(columns={'COKING_COALBLD_GIESFLU': 'liudong'}, inplace=True)
        data_meizhi.rename(columns={'L_M_AB': 'pengzhang'}, inplace=True)
        data_meizhi.rename(columns={'C': 'cc'}, inplace=True)
        data_meizhi.rename(columns={'COKE_HOTVALUE': 're'}, inplace=True)
        data_meizhi_1 = data_meizhi[['PROD_CODE', 'shui', 'hui', 'huifa', 'liu', 'gzhi', 'yzhi', 'liudong', 'pengzhang', 'cc', 're']]
        v = ['PROD_CODE']
        df3 = pd.merge(data_mark, data_meizhi_1, on=v, how='left')
        df3.fillna(max_constant, inplace=True)
        for index, row in df3.iterrows():
            exec('shui_{} ={}'.format(row['mark'], row['shui']))
            exec('hui_{} ={}'.format(row['mark'], row['hui']))
            exec('huifa_{} ={}'.format(row['mark'], row['huifa']))
            exec('liu_{} ={}'.format(row['mark'], row['liu']))
            exec('gzhi_{} ={}'.format(row['mark'], row['gzhi']))
            exec('yzhi_{} ={}'.format(row['mark'], row['yzhi']))
            exec('liudong_{} ={}'.format(row['mark'], row['liudong']))
            exec('pengzhang_{} ={}'.format(row['mark'], row['pengzhang']))
            exec('cc_{} ={}'.format(row['mark'], row['cc']))
            exec('re_{} ={}'.format(row['mark'], row['re']))
        # 煤质上下限
        data_shangxiaxian_1 = data_shangxiaxian[
            (data_shangxiaxian['FLAG'] == '上限') & (data_shangxiaxian['BIG_VAR'] == '炼焦煤')]
        data_shangxiaxian_1 = data_shangxiaxian_1.reset_index(drop=True)
        data_shangxiaxian_1.fillna(max_constant, inplace=True)
        lianjiao_shui_ul = data_shangxiaxian_1.loc[0]['H2O']
        lianjiao_hui_ul = data_shangxiaxian_1.loc[0]['ASH']
        lianjiao_huifa_ul = data_shangxiaxian_1.loc[0]['COKE_VM']
        lianjiao_liu_ul = data_shangxiaxian_1.loc[0]['S']
        lianjiao_gzhi_ul = data_shangxiaxian_1.loc[0]['COKING_COALBLD_BOND_IND']
        lianjiao_yzhi_ul = data_shangxiaxian_1.loc[0]['M_L3_JZCHD_Y']
        lianjiao_liudong_ul = data_shangxiaxian_1.loc[0]['COKING_COALBLD_GIESFLU']
        lianjiao_pengzhang_ul = data_shangxiaxian_1.loc[0]['L_M_AB']
        lianjiao_cc_ul = data_shangxiaxian_1.loc[0]['C']
        lianjiao_re_ul = data_shangxiaxian_1.loc[0]['COKE_HOTVALUE']
        data_shangxiaxian_2 = data_shangxiaxian[
            (data_shangxiaxian['FLAG'] == '下限') & (data_shangxiaxian['BIG_VAR'] == '炼焦煤')]
        data_shangxiaxian_2 = data_shangxiaxian_2.reset_index(drop=True)
        data_shangxiaxian_2.fillna(0, inplace=True)
        lianjiao_shui_ll = data_shangxiaxian_2.loc[0]['H2O']
        lianjiao_hui_ll = data_shangxiaxian_2.loc[0]['ASH']
        lianjiao_huifa_ll = data_shangxiaxian_2.loc[0]['COKE_VM']
        lianjiao_liu_ll = data_shangxiaxian_2.loc[0]['S']
        lianjiao_gzhi_ll = data_shangxiaxian_2.loc[0]['COKING_COALBLD_BOND_IND']
        lianjiao_yzhi_ll = data_shangxiaxian_2.loc[0]['M_L3_JZCHD_Y']
        lianjiao_liudong_ll = data_shangxiaxian_2.loc[0]['COKING_COALBLD_GIESFLU']
        lianjiao_pengzhang_ll = data_shangxiaxian_2.loc[0]['L_M_AB']
        lianjiao_cc_ll = data_shangxiaxian_2.loc[0]['C']
        lianjiao_re_ll = data_shangxiaxian_2.loc[0]['COKE_HOTVALUE']

        # 其他配置的参数(灰中成分，煤岩分析，焦炭参数)
        # sql = " select VAR,CLASS,SOURCE,PROD_DSCR,PROD_CODE,FE2O3,SIO2,CAO,AL2O3,MGO,FOP_TIO2, " \
        #       " MNO,P2O5,BF_SINTER_QLTY_S,ZN,PB,FOP_K2O,FOP_NA2O,VITRI_REFL,VITRI_COAL_OI " \
        # 灰中成分
        data_huizhongchengfen.rename(columns={'FE2O3': 'hui_FE2O3'}, inplace=True)
        data_huizhongchengfen.rename(columns={'SIO2': 'hui_SIO2'}, inplace=True)
        data_huizhongchengfen.rename(columns={'CAO': 'hui_CAO'}, inplace=True)
        data_huizhongchengfen.rename(columns={'AL2O3': 'hui_AL2O3'}, inplace=True)
        data_huizhongchengfen.rename(columns={'MGO': 'hui_MGO'}, inplace=True)
        data_huizhongchengfen.rename(columns={'FOP_TIO2': 'hui_TIO2'}, inplace=True)
        data_huizhongchengfen.rename(columns={'MNO': 'hui_MNO'}, inplace=True)
        data_huizhongchengfen.rename(columns={'P2O5': 'hui_P2O5'}, inplace=True)
        data_huizhongchengfen.rename(columns={'BF_SINTER_QLTY_S': 'hui_S'}, inplace=True)
        data_huizhongchengfen.rename(columns={'ZN': 'hui_ZN'}, inplace=True)
        data_huizhongchengfen.rename(columns={'PB': 'hui_PB'}, inplace=True)
        data_huizhongchengfen.rename(columns={'FOP_K2O': 'hui_K2O'}, inplace=True)
        data_huizhongchengfen.rename(columns={'FOP_NA2O': 'hui_NA2O'}, inplace=True)
        data_huizhongchengfen.rename(columns={'VITRI_REFL': 'meiyan_R'}, inplace=True)
        data_huizhongchengfen.rename(columns={'VITRI_COAL_OI': 'meitan_Ti'}, inplace=True)
        data_huizhongchengfen_1 = data_huizhongchengfen[
            ['PROD_CODE', 'hui_FE2O3', 'hui_SIO2', 'hui_CAO', 'hui_AL2O3', 'hui_MGO', 'hui_TIO2', 'hui_MNO', 'hui_P2O5', 'hui_S', 'hui_ZN', 'hui_PB', 'hui_K2O', 'hui_NA2O', 'meiyan_R', 'meitan_Ti']]
        v = ['PROD_CODE']
        df3 = pd.merge(data_mark, data_huizhongchengfen_1, on=v, how='left')
        df3.fillna(max_constant, inplace=True)
        for index, row in df3.iterrows():
            exec('hui_FE2O3_{} ={}'.format(row['mark'], row['hui_FE2O3']))
            exec('hui_SIO2_{} ={}'.format(row['mark'], row['hui_SIO2']))
            exec('hui_CAO_{} ={}'.format(row['mark'], row['hui_CAO']))
            exec('hui_AL2O3_{} ={}'.format(row['mark'], row['hui_AL2O3']))
            exec('hui_MGO_{} ={}'.format(row['mark'], row['hui_MGO']))
            exec('hui_TIO2_{} ={}'.format(row['mark'], row['hui_TIO2']))
            exec('hui_MNO_{} ={}'.format(row['mark'], row['hui_MNO']))
            exec('hui_P2O5_{} ={}'.format(row['mark'], row['hui_P2O5']))
            exec('hui_S_{} ={}'.format(row['mark'], row['hui_S']))
            exec('hui_ZN_{} ={}'.format(row['mark'], row['hui_ZN']))
            exec('hui_PB_{} ={}'.format(row['mark'], row['hui_PB']))
            exec('hui_K2O_{} ={}'.format(row['mark'], row['hui_K2O']))
            exec('hui_NA2O_{} ={}'.format(row['mark'], row['hui_NA2O']))
            exec('meiyan_R_{} ={}'.format(row['mark'], row['meiyan_R']))
            exec('meitan_Ti_{} ={}'.format(row['mark'], row['meitan_Ti']))

        # 构建系数矩阵
        coef_df = data_mark[['mark']]
        mark_list = coef_df['mark'].to_list()
        array_len = len(mark_list)
        # 构建所有参数对应的约束df
        canshu_df = pd.DataFrame(
            columns=['index', 'coef_name', 'step', 'mark', 'vvar', 'source', 'group', 'meizhi', 'merge_flag'])
        dict_canshu = {}
        j_start = 0
        # 炼焦煤总量,暂认为干煤量
        # 等式约束
        import numpy as np
        from numpy import array
        y0 = array([[0] * array_len], dtype=float)
        y0[0, 0] = 1
        mark_tmp_0 = mark_list[0]
        ldict100 = {}
        exec('m0 = array([ziyuanliang_{}])'.format(mark_tmp_0), locals(), ldict100)
        m0 = ldict100["m0"]
        #默认第一个约束条件是通的
        # if mark_tmp_0[0] == '1':
        #     buzhou_tmp = '主焦'
        # elif mark_tmp_0[0] == '2':
        #     buzhou_tmp = '肥煤'
        # elif mark_tmp_0[0] == '3':
        #     buzhou_tmp = '1/3焦'
        # elif mark_tmp_0[0] == '4':
        #     buzhou_tmp = '气煤'
        # elif mark_tmp_0[0] == '5':
        #     buzhou_tmp = '瘦煤'
        # dict_canshu['index'] = 0
        # dict_canshu['coef_name'] = '资源量'
        # dict_canshu['step'] = '确定煤种以及资源量'
        # dict_canshu['mark'] = mark_tmp_0
        # new_row = pd.Series(dict_canshu)
        # canshu_df = canshu_df.append(new_row, ignore_index=True)
        # dict_canshu = {}

        j_start = j_start + 1
        e2 = array([[1] * array_len], dtype=float)
        f2 = array([peihemei_wt])
        # 不等式约束
        # 资源量
        # 品名资源量
        for j in range(j_start, j_start + int(array_len)-1):
            exec('y{} = array([[0] * array_len],dtype=float)'.format(j))
            exec('y{}[0,j-j_start+1] = 1'.format(j))
            mark_tmp = mark_list[j]
            exec('m{} = array([ziyuanliang_{}])'.format(j, mark_tmp))
            dict_canshu['index'] = j
            dict_canshu['coef_name'] = '资源量'
            if mark_tmp[0] == '1':
                buzhou_tmp = '主焦'
            elif mark_tmp[0] == '2':
                buzhou_tmp = '肥煤'
            elif mark_tmp[0] == '3':
                buzhou_tmp = '1/3焦'
            elif mark_tmp[0] == '4':
                buzhou_tmp = '气煤'
            elif mark_tmp[0] == '5':
                buzhou_tmp = '瘦煤'
            dict_canshu['step'] = '确定煤种以及资源量'
            dict_canshu['mark'] = mark_tmp
            new_row = pd.Series(dict_canshu)
            canshu_df = canshu_df.append(new_row, ignore_index=True)
            dict_canshu = {}
        j_start = j_start + int(array_len)-1
        # 合并单元格资源量

        for j in range(j_start, int(merge_num) + j_start):
            print(j)
            exec('y{} = array([[0] * array_len],dtype=float)'.format(j))
            data_mark_merge = data_mark[(data_mark['MERGE_FLAG'] == j - j_start + 1)]
            data_mark_merge = data_mark_merge.reset_index(drop=True)
            var_tmp = data_mark_merge.loc[0]['VAR']

            data_mark_merge['if_merge'] = 1
            yuechukucun_sum = data_mark_merge['INIT_INV'].sum()
            data_mark_merge = data_mark_merge[['mark', 'if_merge']]
            v = ['mark']
            df3_merge = pd.merge(coef_df, data_mark_merge, on=v, how='left')
            df3_merge.if_merge.fillna(0, inplace=True)
            for index, row in df3_merge.iterrows():
                exec('y{}[0,index] = row["if_merge"]'.format(j))
            exec('m{} = array([merge_ziyuanliang_{}+yuechukucun_sum])'.format(j, j - j_start + 1))

            dict_canshu['index'] = j
            dict_canshu['coef_name'] = '合并可供资源量'
            dict_canshu['step'] = '确定煤种以及资源量'
            dict_canshu['merge_flag'] = j - j_start + 1
            new_row = pd.Series(dict_canshu)
            canshu_df = canshu_df.append(new_row, ignore_index=True)
            dict_canshu = {}

        j_start = j_start + int(merge_num)
        # 分配比例
        # 性状分配比例
        ldict1 = {}
        ldict2 = {}
        for i in range(1, len(var_list) + 1):
            var_tmp = str(i)
            # 上限
            coef_df1 = coef_df.copy()
            exec('true_value = 100 - ul_var_{}'.format(var_tmp), locals(), ldict1)
            true_value = ldict1["true_value"]
            # print(true_value)
            exec('false_value = - ul_var_{}'.format(var_tmp), locals(), ldict1)
            false_value = ldict1["false_value"]

            # print(false_value)
            def __cal_coef(x):
                if x.mark[0] == var_tmp:
                    rst = true_value
                else:
                    rst = false_value
                return rst

            coef_df1['coef'] = coef_df1.apply(lambda x: __cal_coef(x), axis=1)
            exec("y{} = array([[0]*array_len],dtype=float)".format(j_start))
            for index, row in coef_df1.iterrows():
                exec("y{}[0, index] = row['coef']".format(j_start))
            exec("m{} =  array([0])".format(j_start))
            dict_canshu['index'] = j_start
            dict_canshu['coef_name'] = '性状比例上限'
            dict_canshu['step'] = '维度比例上下限'
            dict_canshu['vvar'] = var_list[i - 1]
            new_row = pd.Series(dict_canshu)
            canshu_df = canshu_df.append(new_row, ignore_index=True)
            dict_canshu = {}
            j_start = j_start + 1
            # 下限
            coef_df1 = coef_df.copy()
            exec('true_value = ll_var_{} - 100'.format(var_tmp), locals(), ldict2)
            true_value = ldict2["true_value"]
            # print(true_value)
            exec('false_value = ll_var_{}'.format(var_tmp), locals(), ldict2)
            false_value = ldict2["false_value"]

            # print(false_value)
            def __cal_coef(x):
                if x.mark[0] == var_tmp:
                    rst = true_value
                else:
                    rst = false_value
                return rst

            coef_df1['coef'] = coef_df1.apply(lambda x: __cal_coef(x), axis=1)
            exec("y{} = array([[0]*array_len],dtype=float)".format(j_start))
            for index, row in coef_df1.iterrows():
                exec("y{}[0, index] = row['coef']".format(j_start))
            exec("m{} =  array([0])".format(j_start))
            dict_canshu['index'] = j_start
            dict_canshu['coef_name'] = '性状比例下限'
            dict_canshu['step'] = '维度比例上下限'
            dict_canshu['vvar'] = var_list[i - 1]
            new_row = pd.Series(dict_canshu)
            canshu_df = canshu_df.append(new_row, ignore_index=True)
            dict_canshu = {}
            j_start = j_start + 1
        # 特殊，主焦+肥煤
        # 上限
        coef_df1 = coef_df.copy()
        true_value = 100 - teshu_ul
        false_value = - teshu_ul
        # print(false_value)
        def __cal_coef(x):
            print(x.mark[0])
            if x.mark[0] == '1' or x.mark[0] == '2':
                rst = true_value
            else:
                rst = false_value
            return rst

        coef_df1['coef'] = coef_df1.apply(lambda x: __cal_coef(x), axis=1)
        exec("y{} = array([[0]*array_len],dtype=float)".format(j_start))
        for index, row in coef_df1.iterrows():
            exec("y{}[0, index] = row['coef']".format(j_start))
        exec("m{} =  array([0])".format(j_start))
        dict_canshu['index'] = j_start
        dict_canshu['coef_name'] = '特殊比例上限'
        dict_canshu['step'] = '维度比例上下限'
        new_row = pd.Series(dict_canshu)
        canshu_df = canshu_df.append(new_row, ignore_index=True)
        dict_canshu = {}
        j_start = j_start + 1
        # 下限
        coef_df1 = coef_df.copy()
        true_value = teshu_ll - 100
        false_value = teshu_ll
        def __cal_coef(x):
            if x.mark[0] == '1' or x.mark[0] == '2':
                rst = true_value
            else:
                rst = false_value
            return rst

        coef_df1['coef'] = coef_df1.apply(lambda x: __cal_coef(x), axis=1)
        exec("y{} = array([[0]*array_len],dtype=float)".format(j_start))
        for index, row in coef_df1.iterrows():
            exec("y{}[0, index] = row['coef']".format(j_start))
        exec("m{} =  array([0])".format(j_start))
        dict_canshu['index'] = j_start
        dict_canshu['coef_name'] = '特殊比例下限'
        dict_canshu['step'] = '维度比例上下限'
        new_row = pd.Series(dict_canshu)
        canshu_df = canshu_df.append(new_row, ignore_index=True)
        dict_canshu = {}
        j_start = j_start + 1


        # 来源分配比例
        ldict3 = {}
        ldict4 = {}
        for i in range(1, len(source_list) + 1):
            source_tmp = str(i)
            # 上限
            coef_df1 = coef_df.copy()
            exec('true_value = 100 - ul_source_{}'.format(source_tmp), locals(), ldict3)
            true_value = ldict3["true_value"]
            # print(true_value)
            exec('false_value = - ul_source_{}'.format(source_tmp), locals(), ldict3)
            false_value = ldict3["false_value"]

            # print(false_value)
            def __cal_coef(x):
                if x.mark[2] == source_tmp:
                    rst = true_value
                else:
                    rst = false_value
                return rst

            coef_df1['coef'] = coef_df1.apply(lambda x: __cal_coef(x), axis=1)
            exec("y{} = array([[0]*array_len],dtype=float)".format(j_start))
            for index, row in coef_df1.iterrows():
                exec("y{}[0, index] = row['coef']".format(j_start))
            exec("m{} =  array([0])".format(j_start))
            dict_canshu['index'] = j_start
            dict_canshu['coef_name'] = '来源比例上限'
            dict_canshu['step'] = '维度比例上下限'
            dict_canshu['source'] = source_list[i - 1]
            new_row = pd.Series(dict_canshu)
            canshu_df = canshu_df.append(new_row, ignore_index=True)
            dict_canshu = {}
            j_start = j_start + 1
            # 下限
            coef_df1 = coef_df.copy()
            exec('true_value = ll_source_{} - 100'.format(source_tmp), locals(), ldict4)
            true_value = ldict4["true_value"]
            # print(true_value)
            exec('false_value = ll_source_{}'.format(source_tmp), locals(), ldict4)
            false_value = ldict4["false_value"]

            # print(false_value)
            def __cal_coef(x):
                if x.mark[2] == source_tmp:
                    rst = true_value
                else:
                    rst = false_value
                return rst

            coef_df1['coef'] = coef_df1.apply(lambda x: __cal_coef(x), axis=1)
            exec("y{} = array([[0]*array_len],dtype=float)".format(j_start))
            for index, row in coef_df1.iterrows():
                exec("y{}[0, index] = row['coef']".format(j_start))
            exec("m{} =  array([0])".format(j_start))
            dict_canshu['index'] = j_start
            dict_canshu['coef_name'] = '来源比例下限'
            dict_canshu['step'] = '维度比例上下限'
            dict_canshu['source'] = source_list[i - 1]
            new_row = pd.Series(dict_canshu)
            canshu_df = canshu_df.append(new_row, ignore_index=True)
            dict_canshu = {}
            j_start = j_start + 1
        # 性状来源分配比例
        group_list = var_df['group'].to_list()
        ldict5 = {}
        ldict6 = {}
        for group_tmp in group_list:
            # 上限
            coef_df1 = coef_df.copy()
            exec('true_value = 100 - ul_group_{}'.format(group_tmp), locals(), ldict5)
            true_value = ldict5["true_value"]
            # print(true_value)
            exec('false_value = - ul_group_{}'.format(group_tmp), locals(), ldict5)
            false_value = ldict5["false_value"]

            # print(false_value)
            def __cal_coef(x):
                if x.mark[0:3] == group_tmp:
                    rst = true_value
                else:
                    rst = false_value
                return rst

            coef_df1['coef'] = coef_df1.apply(lambda x: __cal_coef(x), axis=1)
            exec("y{} = array([[0]*array_len],dtype=float)".format(j_start))
            for index, row in coef_df1.iterrows():
                exec("y{}[0, index] = row['coef']".format(j_start))
            exec("m{} =  array([0])".format(j_start))
            dict_canshu['index'] = j_start
            dict_canshu['coef_name'] = '性状来源比例上限'
            dict_canshu['step'] = '维度比例上下限'
            if group_tmp[0] == '1':
                var_tmp = '主焦'
            elif group_tmp[0] == '2':
                var_tmp = '肥煤'
            elif group_tmp[0] == '3':
                var_tmp = '1/3焦'
            elif group_tmp[0] == '4':
                var_tmp = '气煤'
            elif group_tmp[0] == '5':
                var_tmp = '瘦煤'
            dict_canshu['vvar'] = var_tmp
            if group_tmp[2] == '1':
                source_tmp = '进口'
            elif group_tmp[2] == '2':
                source_tmp = '大矿长协'
            elif group_tmp[2] == '3':
                source_tmp = '大矿市场'
            elif group_tmp[2] == '4':
                source_tmp = '地方矿'
            dict_canshu['source'] = source_tmp
            dict_canshu['group'] = group_tmp
            new_row = pd.Series(dict_canshu)
            canshu_df = canshu_df.append(new_row, ignore_index=True)
            dict_canshu = {}
            j_start = j_start + 1
            # 下限
            coef_df1 = coef_df.copy()
            exec('true_value = ll_group_{} - 100'.format(group_tmp), locals(), ldict6)
            true_value = ldict6["true_value"]
            # print(true_value)
            exec('false_value = ll_group_{}'.format(group_tmp), locals(), ldict6)
            false_value = ldict6["false_value"]

            # print(false_value)
            def __cal_coef(x):
                if x.mark[0:3] == group_tmp:
                    rst = true_value
                else:
                    rst = false_value
                return rst

            coef_df1['coef'] = coef_df1.apply(lambda x: __cal_coef(x), axis=1)
            exec("y{} = array([[0]*array_len],dtype=float)".format(j_start))
            for index, row in coef_df1.iterrows():
                exec("y{}[0, index] = row['coef']".format(j_start))
            exec("m{} =  array([0])".format(j_start))
            dict_canshu['index'] = j_start
            dict_canshu['coef_name'] = '性状来源比例下限'
            dict_canshu['step'] = '维度比例上下限'
            if group_tmp[0] == '1':
                var_tmp = '主焦'
            elif group_tmp[0] == '2':
                var_tmp = '肥煤'
            elif group_tmp[0] == '3':
                var_tmp = '1/3焦'
            elif group_tmp[0] == '4':
                var_tmp = '气煤'
            elif group_tmp[0] == '5':
                var_tmp = '瘦煤'
            dict_canshu['vvar'] = var_tmp
            if group_tmp[2] == '1':
                source_tmp = '进口'
            elif group_tmp[2] == '2':
                source_tmp = '大矿长协'
            elif group_tmp[2] == '3':
                source_tmp = '大矿市场'
            elif group_tmp[2] == '4':
                source_tmp = '地方矿'
            dict_canshu['source'] = source_tmp
            dict_canshu['group'] = group_tmp
            new_row = pd.Series(dict_canshu)
            canshu_df = canshu_df.append(new_row, ignore_index=True)
            dict_canshu = {}
            j_start = j_start + 1
        # 品名分配比例
        ldict7 = {}
        ldict8 = {}
        for mark_tmp in mark_list:
            # 上限
            coef_df1 = coef_df.copy()
            exec('true_value = 100 - ul_{}'.format(mark_tmp), locals(), ldict7)
            true_value = ldict7["true_value"]
            # print(true_value)
            exec('false_value = - ul_{}'.format(mark_tmp), locals(), ldict7)
            false_value = ldict7["false_value"]

            # print(false_value)
            def __cal_coef(x):
                if x.mark == mark_tmp:
                    rst = true_value
                else:
                    rst = false_value
                return rst

            coef_df1['coef'] = coef_df1.apply(lambda x: __cal_coef(x), axis=1)
            exec("y{} = array([[0]*array_len],dtype=float)".format(j_start))
            for index, row in coef_df1.iterrows():
                exec("y{}[0, index] = row['coef']".format(j_start))
            exec("m{} =  array([0])".format(j_start))
            dict_canshu['index'] = j_start
            dict_canshu['coef_name'] = '品种比例上限'
            if mark_tmp[0] == '1':
                buzhou_tmp = '主焦'
            elif mark_tmp[0] == '2':
                buzhou_tmp = '肥煤'
            elif mark_tmp[0] == '3':
                buzhou_tmp = '1/3焦'
            elif mark_tmp[0] == '4':
                buzhou_tmp = '气煤'
            elif mark_tmp[0] == '5':
                buzhou_tmp = '瘦煤'
            dict_canshu['step'] = buzhou_tmp
            dict_canshu['mark'] = mark_tmp
            new_row = pd.Series(dict_canshu)
            canshu_df = canshu_df.append(new_row, ignore_index=True)
            dict_canshu = {}
            j_start = j_start + 1
            # 下限
            coef_df1 = coef_df.copy()
            exec('true_value = ll_{} - 100'.format(mark_tmp), locals(), ldict8)
            true_value = ldict8["true_value"]
            # print(true_value)
            exec('false_value = ll_{}'.format(mark_tmp), locals(), ldict8)
            false_value = ldict8["false_value"]

            # print(false_value)
            def __cal_coef(x):
                if x.mark == mark_tmp:
                    rst = true_value
                else:
                    rst = false_value
                return rst

            coef_df1['coef'] = coef_df1.apply(lambda x: __cal_coef(x), axis=1)
            exec("y{} = array([[0]*array_len],dtype=float)".format(j_start))
            for index, row in coef_df1.iterrows():
                exec("y{}[0, index] = row['coef']".format(j_start))
            exec("m{} =  array([0])".format(j_start))
            dict_canshu['index'] = j_start
            dict_canshu['coef_name'] = '品种比例下限'
            if mark_tmp[0] == '1':
                buzhou_tmp = '主焦'
            elif mark_tmp[0] == '2':
                buzhou_tmp = '肥煤'
            elif mark_tmp[0] == '3':
                buzhou_tmp = '1/3焦'
            elif mark_tmp[0] == '4':
                buzhou_tmp = '气煤'
            elif mark_tmp[0] == '5':
                buzhou_tmp = '瘦煤'
            dict_canshu['step'] = buzhou_tmp
            dict_canshu['mark'] = mark_tmp
            new_row = pd.Series(dict_canshu)
            canshu_df = canshu_df.append(new_row, ignore_index=True)
            dict_canshu = {}
            j_start = j_start + 1
        # 煤质
        meizhi_list = ['shui', 'hui', 'huifa', 'liu', 'gzhi', 'yzhi', 'liudong', 'pengzhang', 'cc', 're']
        big_var_tmp = 'lianjiao'
        for meizhi_tmp in meizhi_list:
            coef_df1 = coef_df.copy()
            # 上限
            exec("y{} = array([[0]*array_len],dtype=float)".format(j_start))
            for index, row in coef_df1.iterrows():
                mark_tmp = row['mark']
                exec("y{}[0, index] = {}_{} - {}_{}_ul".format(j_start, meizhi_tmp, mark_tmp, big_var_tmp,
                                                               meizhi_tmp))
            exec("m{} =  array([0])".format(j_start))
            dict_canshu['index'] = j_start
            dict_canshu['coef_name'] = '上限'
            dict_canshu['step'] = '煤质上下限'
            dict_canshu['meizhi'] = meizhi_tmp
            new_row = pd.Series(dict_canshu)
            canshu_df = canshu_df.append(new_row, ignore_index=True)
            dict_canshu = {}
            j_start = j_start + 1
            # 下限
            exec("y{} = array([[0]*array_len],dtype=float)".format(j_start))
            for index, row in coef_df1.iterrows():
                mark_tmp = row['mark']
                exec("y{}[0, index] = {}_{}_ll - {}_{}".format(j_start, big_var_tmp, meizhi_tmp, meizhi_tmp,
                                                               mark_tmp))
            exec("m{} =  array([0])".format(j_start))
            dict_canshu['index'] = j_start
            dict_canshu['coef_name'] = '下限'
            dict_canshu['step'] = '煤质上下限'
            dict_canshu['meizhi'] = meizhi_tmp
            new_row = pd.Series(dict_canshu)
            canshu_df = canshu_df.append(new_row, ignore_index=True)
            dict_canshu = {}
            j_start = j_start + 1

        # 拼接
        ldict0 = {}
        y_str = 'y0'
        # yyy = (y0,y0)
        for j in range(1, j_start):
            y_str = y_str + ',' + 'y' + str(j)
        # exec('yyy =({})'.format(y_str))
        exec('yyy =({})'.format(y_str), locals(), ldict0)
        yyy = ldict0["yyy"]
        Y = np.concatenate(yyy, axis=0)

        m_str = 'm0'
        # mmm = (m0,m0)
        for j in range(1, j_start):
            m_str = m_str + ',' + 'm' + str(j)
        # exec('mmm =({})'.format(m_str))
        exec('mmm =({})'.format(m_str), locals(), ldict0)
        mmm = ldict0["mmm"]
        M = np.concatenate(mmm, axis=0)
        print('finish')
        # 价格
        # cc假定价格为1
        cc = array([[1] * array_len], dtype=float)


        # 判断函数
        def judge_solution_exist(e1, f1):
            """
            传入e1，f1进行线性规划求解，判断是否有可行域
            """
            x = cp.Variable(array_len)
            total_cost = cc @ x


            obj = cp.Minimize(total_cost)
            cons = [e1 @ x <= f1, e2 @ x == f2, x >= 0]
            prob = cp.Problem(obj, cons)
            prob.solve(solver='GLPK_MI', verbose=True)
            # print("最优初始值为:", prob.value)
            # print("最优初始解为：\n", x.value)
            success = 0
            if x.value is None:
                success = 0
            else:
                success = 1
            # print(success)
            return success

        success = judge_solution_exist(e1=Y, f1=M)
        yy = y0
        mm = m0
        listb = []
        if success == 1:
            message = '约束条件合理，存在最优解'
            result_list = []
            # print(message)
        else:
            message = '约束条件不合理，不存在最优解'
            # print(message)
            # print('无解，开始循环找需要修改的约束条件')
            for i in range(1, j_start):
                yi = Y[[i]]
                mi = M[[i]]
                Yy = np.concatenate((yy, yi), axis=0)
                Mm = np.concatenate((mm, mi), axis=0)
                success = judge_solution_exist(e1=Yy, f1=Mm)
                if success == 1:
                    yy = Yy
                    mm = Mm
                else:
                    yy = yy
                    mm = mm
                    # print(i)
                    listb.append(i-1)
                    # print(listb)
            df2 = canshu_df.iloc[listb]
            # print('需要修改的约束条件参数为')
            # print(df2)
        # df2 = canshu_df

            df2 = df2.reset_index(drop=True)
            data_mark_tmp = data_mark[['VAR', 'SOURCE', 'PROD_DSCR', 'mark']]
            data_mark_tmp.rename(columns={'VAR': 'VAR_TMP'}, inplace=True)
            data_mark_tmp.rename(columns={'SOURCE': 'SOURCE_TMP'}, inplace=True)
            data_mark_tmp.rename(columns={'PROD_DSCR': 'PROD_DSCR_TMP'}, inplace=True)

            v = ['mark']
            df2 = pd.merge(df2, data_mark_tmp, on=v, how='left')

            # var_df_tmp = var_df[['group', 'VAR', 'SOURCE']]
            # var_df_tmp.rename(columns={'SOURCE': 'SOURCE_TMP_group'}, inplace=True)
            # var_df_tmp.rename(columns={'VAR': 'VAR_TMP_group'}, inplace=True)
            # v = ['group']
            # df2 = pd.merge(df2, var_df_tmp, on=v, how='left')

            def __cal_coef(x):
                if x.step == '煤质上下限':
                    ind = ''
                    if x.meizhi == 'shui':
                        ind = '水分'
                    elif x.meizhi == 'hui':
                        ind = '灰分'
                    elif x.meizhi == 'huifa':
                        ind = '挥发分'
                    elif x.meizhi == 'liu':
                        ind = '硫分'
                    elif x.meizhi == 'gzhi':
                        ind = '粘结指数G值'
                    elif x.meizhi == 'yzhi':
                        ind = '胶质层厚度Y值'
                    elif x.meizhi == 'liudong':
                        ind = '基氏流动度'
                    elif x.meizhi == 'pengzhang':
                        ind = '奥亚膨胀度'
                    elif x.meizhi == 'cc':
                        ind = 'C'
                    elif x.meizhi == 're':
                        ind = '热值'
                    rst = '炼焦煤' + '_' + ind + '_' + str(x.coef_name)
                elif x.coef_name == '品种比例上限' or x.coef_name == '品种比例下限':
                    rst = str(x.VAR_TMP) + '_' + str(x.SOURCE_TMP) + '_' + str(x.PROD_DSCR_TMP) + '_' + str(x.coef_name)
                elif x.coef_name == '性状比例上限' or x.coef_name == '性状比例下限':
                    rst = str(x.vvar) + '_' + str(x.coef_name)
                elif x.coef_name == '特殊比例上限' or x.coef_name == '特殊比例下限':
                    rst = '主焦+肥煤' + '_' + str(x.coef_name)
                elif x.coef_name == '来源比例上限' or x.coef_name == '来源比例下限':
                    rst = str(x.source) + '_' + str(x.coef_name)
                elif x.coef_name == '性状来源比例上限' or x.coef_name == '性状来源比例下限':
                    rst = str(x.vvar) + '_' + str(x.source) + '_' + str(x.coef_name)
                elif x.coef_name == '资源量':
                    rst = str(x.VAR_TMP) + '_' + str(x.SOURCE_TMP) + '_' + str(x.PROD_DSCR_TMP) + '_' + str(x.coef_name)
                elif x.coef_name == '合并可供资源量':
                    rst = '第' + str(x.merge_flag) + '组' + '_' + str(x.coef_name)
                else:
                    rst = x.coef_name
                return rst

            df2['new_coef'] = df2.apply(lambda x: __cal_coef(x), axis=1)

            def __cal_coef_col_name(x):
                if x.step == '煤质上下限':
                    if x.meizhi == 'shui':
                        rst = 'H2O'
                    elif x.meizhi == 'hui':
                        rst = 'ASH'
                    elif x.meizhi == 'huifa':
                        rst = 'COKE_VM'
                    elif x.meizhi == 'liu':
                        rst = 'S'
                    elif x.meizhi == 'gzhi':
                        rst = 'COKING_COALBLD_BOND_IND'
                    elif x.meizhi == 'yzhi':
                        rst = 'M_L3_JZCHD_Y'
                    elif x.meizhi == 'liudong':
                        rst = 'COKING_COALBLD_GIESFLU'
                    elif x.meizhi == 'pengzhang':
                        rst = 'L_M_AB'
                    elif x.meizhi == 'cc':
                        rst = 'C'
                    elif x.meizhi == 're':
                        rst = 'COKE_HOTVALUE'
                elif x.coef_name == '品种比例上限':
                    rst = 'MAX_VALUE'
                elif x.coef_name == '品种比例下限':
                    rst = 'MIN_VALUE'
                elif x.coef_name == '性状比例上限':
                    rst = 'MAX_VALUE'
                elif x.coef_name == '性状比例下限':
                    rst = 'MIN_VALUE'
                elif x.coef_name == '特殊比例上限':
                    rst = 'MAX_VALUE'
                elif x.coef_name == '特殊比例下限':
                    rst = 'MIN_VALUE'
                elif x.coef_name == '来源比例上限':
                    rst = 'MAX_VALUE'
                elif x.coef_name == '来源比例下限':
                    rst = 'MIN_VALUE'
                elif x.coef_name == '性状来源比例上限':
                    rst = 'MAX_VALUE'
                elif x.coef_name == '性状来源比例下限':
                    rst = 'MIN_VALUE'
                elif x.coef_name == '资源量':
                    rst = 'RESOURCE_WT'
                elif x.coef_name == '合并可供资源量':
                    rst = 'INV_WT'
                return rst

            df2['new_coef_col_name'] = df2.apply(lambda x: __cal_coef_col_name(x), axis=1)

            def __cal_coef_index_name(x):
                if x.coef_name == '上限':
                    rst = '上限'
                elif x.coef_name == '下限':
                    rst = '下限'
                elif x.coef_name == '品种比例上限' or x.coef_name == '品种比例下限':
                    rst = x.PROD_DSCR_TMP
                elif x.coef_name == '性状比例上限' or x.coef_name == '性状比例下限':
                    rst = x.vvar
                elif x.coef_name == '特殊比例上限' or x.coef_name == '特殊比例下限':
                    rst = '主焦+肥煤'
                elif x.coef_name == '来源比例上限' or x.coef_name == '来源比例下限':
                    rst = x.source
                elif x.coef_name == '性状来源比例上限' or x.coef_name == '性状来源比例下限':
                    rst = str(x.vvar) + '_' + str(x.source)
                elif x.coef_name == '资源量':
                    rst = x.PROD_DSCR_TMP
                elif x.coef_name == '合并可供资源量':
                    rst = x.merge_flag
                return rst

            df2['new_coef_index_name'] = df2.apply(lambda x: __cal_coef_index_name(x), axis=1)
            df_out = pd.DataFrame(columns=['COEF_NAME', 'STEP_NAME', 'TABLE_NAME', 'INDEX_NUM', 'COL_NUM'])
            dict = {}
            message = message + '需要修改的约束条件参数为：'
            for index, row in df2.iterrows():
                dict['COEF_NAME'] = row['new_coef']
                message = message + row['new_coef'] + '；'
                dict['STEP_NAME'] = row['step']
                if row['coef_name'] == '性状比例上限' or row['coef_name'] == '性状比例下限':
                    dict['TABLE_NAME'] = '表1'
                elif row['coef_name'] == '来源比例上限' or row['coef_name'] == '来源比例下限':
                    dict['TABLE_NAME'] = '表2'
                elif row['coef_name'] == '性状来源比例上限' or row['coef_name'] == '性状来源比例下限':
                    dict['TABLE_NAME'] = '表3'
                elif row['coef_name'] == '特殊比例上限' or row['coef_name'] == '特殊比例下限':
                    dict['TABLE_NAME'] = '表4'
                elif row['coef_name'] == '品种比例上限' or row['coef_name'] == '品种比例下限':
                    dict['TABLE_NAME'] = '表1'
                elif row['coef_name'] == '资源量':
                    dict['TABLE_NAME'] = '表1'
                else:
                    dict['TABLE_NAME'] = ''
                if row['step'] == '煤质上下限':
                    # sql = ""
                    # sql = " select BIG_VAR,FLAG,H2O,ASH,COKE_VM,S, " \
                    #       " COKING_COALBLD_BOND_IND,M_L3_JZCHD_Y,COKING_COALBLD_GIESFLU,L_M_AB,C,COKE_HOTVALUE " \
                    #       " from BG00MAZZAI.T_ADS_WH_YLMX_COKE_INDLIMIT2 " \
                    #       " where TMPL_NO ='%s' order by REC_ID" % (tmpl_no)
                    # data_tmp = pd.read_sql_query(sql, con=db_conn_mpp)
                    data_tmp = pd.read_excel('COKE2煤质上下限.xlsx')
                    data_tmp.columns = data_tmp.columns.str.upper()
                    flag_tmp = row['coef_name']
                    ind_tmp = row['new_coef_col_name']
                    row_index = data_tmp.loc[(data_tmp['FLAG'] == flag_tmp)].index[0]
                    column_index = data_tmp.columns.get_loc(ind_tmp)
                    dict['INDEX_NUM'] = row_index
                    dict['COL_NUM'] = column_index
                elif row['coef_name'] == '性状比例上限' or row['coef_name'] == '性状比例下限':
                    # sql = ""
                    sql = " select VAR,MIN_VALUE,MAX_VALUE " \
                          " from BG00MAZZAI.T_ADS_WH_YLMX_COKE_RATIO2 " \
                          " where TMPL_NO ='%s' and FLAG='性状' order by REC_ID" % (tmpl_no)
                    data_tmp = pd.read_sql_query(sql, con=db_conn_mpp)
                    data_tmp.columns = data_tmp.columns.str.upper()
                    var_tmp = row['vvar']
                    ind_tmp = row['new_coef_col_name']
                    row_index = data_tmp.loc[data_tmp['VAR'] == var_tmp].index[0]  # 找到满足条件的第一行的行索引
                    column_index = data_tmp.columns.get_loc(ind_tmp)
                    dict['INDEX_NUM'] = row_index
                    dict['COL_NUM'] = column_index
                elif row['coef_name'] == '特殊比例上限' or row['coef_name'] == '特殊比例下限':
                    row_index = 0  # 找到满足条件的第一行的行索引
                    if row['coef_name'] == '特殊比例下限':
                        column_index = 1
                    elif row['coef_name'] == '特殊比例上限':
                        column_index = 2
                    dict['INDEX_NUM'] = row_index
                    dict['COL_NUM'] = column_index
                elif row['coef_name'] == '来源比例上限' or row['coef_name'] == '来源比例下限':
                    # sql = ""
                    sql = " select SOURCE,MIN_VALUE,MAX_VALUE " \
                          " from BG00MAZZAI.T_ADS_WH_YLMX_COKE_RATIO2 " \
                          " where TMPL_NO ='%s' and FLAG='来源' order by REC_ID" % (tmpl_no)
                    data_tmp = pd.read_sql_query(sql, con=db_conn_mpp)
                    data_tmp.columns = data_tmp.columns.str.upper()
                    source_tmp = row['source']
                    ind_tmp = row['new_coef_col_name']
                    row_index = data_tmp.loc[data_tmp['SOURCE'] == source_tmp].index[0]  # 找到满足条件的第一行的行索引
                    column_index = data_tmp.columns.get_loc(ind_tmp)
                    dict['INDEX_NUM'] = row_index
                    dict['COL_NUM'] = column_index
                elif row['coef_name'] == '性状来源比例上限' or row['coef_name'] == '性状来源比例下限':
                    # sql = ""
                    sql = " select VAR,SOURCE,MIN_VALUE,MAX_VALUE " \
                          " from BG00MAZZAI.T_ADS_WH_YLMX_COKE_RATIO2 " \
                          " where TMPL_NO ='%s' and FLAG='性状来源' order by REC_ID" % (tmpl_no)
                    data_tmp = pd.read_sql_query(sql, con=db_conn_mpp)
                    data_tmp.columns = data_tmp.columns.str.upper()
                    var_tmp = row['vvar']
                    source_tmp = row['source']
                    ind_tmp = row['new_coef_col_name']
                    row_index = data_tmp.loc[(data_tmp['VAR'] == var_tmp) & (data_tmp['SOURCE'] == source_tmp)].index[0]  # 找到满足条件的第一行的行索引
                    column_index = data_tmp.columns.get_loc(ind_tmp)
                    dict['INDEX_NUM'] = row_index
                    dict['COL_NUM'] = column_index
                elif row['coef_name'] == '品种比例上限' or row['coef_name'] == '品种比例下限':
                    var_tmp = row['step']
                    sql = " select t1.VAR,t1.CLASS,t1.SOURCE,t1.PROD_DSCR,t1.PROD_CODE,t2.MIN_VALUE,t2.MAX_VALUE,t3.ASH,t3.COKE_VM,t3.S " \
                          " from ( " \
                          " select REC_ID,VAR,CLASS,SOURCE,PROD_DSCR,PROD_CODE  " \
                          " from BG00MAZZAI.T_ADS_WH_YLMX_COKE_KIND_INFO2 " \
                          " where TMPL_NO ='%s' and VAR='%s' " \
                          " ) as t1 left join ( " \
                          " select PROD_CODE,MIN_VALUE,MAX_VALUE " \
                          " from BG00MAZZAI.T_ADS_WH_YLMX_COKE_RATIO2 " \
                          " where TMPL_NO ='%s' and FLAG='品名' and VAR='%s' " \
                          " ) as t2 on t1.PROD_CODE=t2.PROD_CODE " \
                          " left join ( " \
                          " select PROD_CODE,H2O,ASH,COKE_VM,S " \
                          " COKING_COALBLD_BOND_IND,M_L3_JZCHD_Y,COKING_COALBLD_GIESFLU,L_M_AB,C,COKE_HOTVALUE " \
                          " from BG00MAZZAI.T_ADS_WH_YLMX_COKE_IND_INFO2 " \
                          " where TMPL_NO ='%s'  " \
                          " ) as t3 " \
                          " on t1.PROD_CODE=t3.PROD_CODE order by t1.REC_ID " % (tmpl_no, var_tmp, tmpl_no, var_tmp, tmpl_no)
                    data_tmp = pd.read_sql_query(sql, con=db_conn_mpp)
                    data_tmp.columns = data_tmp.columns.str.upper()
                    prod_dscr_tmp = row['PROD_DSCR_TMP']
                    ind_tmp = row['new_coef_col_name']
                    row_index = data_tmp.loc[data_tmp['PROD_DSCR'] == prod_dscr_tmp].index[0]  # 找到满足条件的第一行的行索引
                    column_index = data_tmp.columns.get_loc(ind_tmp)
                    dict['INDEX_NUM'] = row_index
                    dict['COL_NUM'] = column_index
                elif row['coef_name'] == '资源量':
                    # sql = ""
                    sql = " select VAR,CLASS,SOURCE,PROD_DSCR,PROD_CODE,INIT_INV,INV_WT,MERGE_FLAG,UNIT_PRICE " \
                          " from BG00MAZZAI.T_ADS_WH_YLMX_COKE_PRICE2 " \
                          " where TMPL_NO ='%s' order by REC_ID" % (tmpl_no)
                    data_tmp = pd.read_sql_query(sql, con=db_conn_mpp)
                    data_tmp.columns = data_tmp.columns.str.upper()
                    prod_dscr_tmp = row['PROD_DSCR_TMP']
                    ind_tmp = row['new_coef_col_name']
                    row_index = data_tmp.loc[data_tmp['PROD_DSCR'] == prod_dscr_tmp].index[0]  # 找到满足条件的第一行的行索引
                    column_index = data_tmp.columns.get_loc(ind_tmp)
                    dict['INDEX_NUM'] = row_index
                    dict['COL_NUM'] = column_index
                elif row['coef_name'] == '合并可供资源量':
                    # sql = ""
                    sql = " select VAR,CLASS,SOURCE,PROD_DSCR,PROD_CODE,INIT_INV,INV_WT,MERGE_FLAG,UNIT_PRICE " \
                          " from BG00MAZZAI.T_ADS_WH_YLMX_COKE_PRICE2 " \
                          " where TMPL_NO ='%s' order by REC_ID" % (tmpl_no)
                    data_tmp = pd.read_sql_query(sql, con=db_conn_mpp)
                    data_tmp.columns = data_tmp.columns.str.upper()
                    merge_tag_tmp = row['merge_flag']
                    ind_tmp = row['new_coef_col_name']
                    row_index = data_tmp.loc[data_tmp['merge_flag'] == merge_tag_tmp].index[0]  # 找到满足条件的第一行的行索引
                    column_index = data_tmp.columns.get_loc(ind_tmp)
                    dict['INDEX_NUM'] = row_index
                    dict['COL_NUM'] = column_index
                new_row = pd.Series(dict)
                df_out = df_out.append(new_row, ignore_index=True)
            result_list = df_out.to_dict(orient='records')
        return message, result_list